﻿using System;
using System.Collections.Generic;
using System.Text;

namespace GenDB
{
    public class ColumnInfo
    {
        public string columnName = null;
        public string dataType = null;
        public string length = null;
        public string defaultValue = null;
        public bool isPrimaryKey = false;

        public ColumnInfo(string columnName)
        {
            this.columnName = columnName;
        }

        public string GetSqlDataType()
        {
            switch (dataType)
            {
                case "int":
                    return "int";
                case "string":
                    return "nvarchar";
                case "datetime":
                    return "datetime";
                case "bit":
                    return "bit";
                case "float":
                    return "float";
                default:
                    throw new Exception("no defined");
            }
        }
    }

    public class TableInfo
    {
        public string tableName = null;
        public List<ColumnInfo> columnInfos = new List<ColumnInfo>();

        public TableInfo(string tableName)
        {
            this.tableName = tableName;
        }

        public ColumnInfo GetColumnInfo(string columnName)
        {
            foreach (ColumnInfo columnInfo in columnInfos)
            {
                if (columnInfo.columnName == columnName)
                    return columnInfo;
            }
            return null;
        }

        public bool HasColumn(string columnName)
        {
            foreach (ColumnInfo columnInfo in columnInfos)
            {
                if (columnInfo.columnName == columnName)
                    return true;
            }

            return false;
        }

        private string GetPrimaryColumn()
        {
            int i = 0;
            int length = this.columnInfos.Count;
            for (i = 0; i < length; i++)
            {
                if (this.columnInfos[i].isPrimaryKey)
                    return this.columnInfos[i].columnName;
            }

            return null;
        }

        private string GetSQLColumn(ColumnInfo cl)
        {
            string s = "[" + cl.columnName + "] [" + cl.GetSqlDataType() + "]";
            if (cl.isPrimaryKey)
                s += " IDENTITY(1,1) NOT NULL,";
            else
            {
                if (cl.length != null)
                    s += "(" + cl.length + ")";

                if (cl.dataType == "string")
                    s += " COLLATE SQL_Latin1_General_CP1_CI_AS";

                s += " NULL,";
            }

            return s;
        }

        private string GetSQLDescription(ColumnInfo cl)
        {
            if (cl.defaultValue == null)
                return null;
            else
            {
                string s = "EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'[" + cl.defaultValue + "]' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'" + this.tableName + "', @level2type=N'COLUMN', @level2name=N'" + cl.columnName + "'";
                return s;
            }
        }

        public List<string> GetSQLTable()
        {
            int i = 0;
            int length = this.columnInfos.Count;

            List<string> lstCMD = new List<string>();
            string s = "";

            //drop table
            s = "IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + this.tableName + "]') AND type in (N'U'))\r\n";
            s += "DROP TABLE [dbo].[HOSO]\r\n";
            lstCMD.Add(s);

            //create table
            s = "CREATE TABLE [dbo].[" + this.tableName + "](\r\n";

            for (i = 0; i < length; i++)
                s += "\t" + GetSQLColumn(this.columnInfos[i]) + "\r\n";

            s += "CONSTRAINT [PK_" + this.tableName + "] PRIMARY KEY CLUSTERED\r\n";
            s += "(\r\n";
            s += "\t[" + GetPrimaryColumn() + "] ASC\r\n";
            s += ")WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]\r\n";
            s += ") ON [PRIMARY]\r\n";

            lstCMD.Add(s);


            // create desc
            string desc = null;
            s = "";
            for (i = 0; i < length; i++)
            {
                desc = GetSQLDescription(this.columnInfos[i]);
                if (desc != null)
                    lstCMD.Add(desc);
            }


            return lstCMD;
        }
    }
}
